﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using NorthwindDBO;
using System.Data.SqlClient;
using System.Data.Objects;
using System.Data.Entity.ModelConfiguration;

namespace AddRemoveUpdateCustomer
{
    class CustomerModifier
    {
        static void AddCustomer(string customerId, string companyName)
        {
            NorthwindEntities entities = new NorthwindEntities();
            Customer cust = new Customer
            {
                CustomerID = customerId,
                CompanyName = companyName
            };
            entities.Customers.AddObject(cust);
            entities.SaveChanges();
        }

        static void DeleteCustomer(string customerId)
        {
            NorthwindEntities entities = new NorthwindEntities();
            Customer customerToDelete = GetCustomerById(customerId, entities);
            entities.Customers.DeleteObject(customerToDelete);
            entities.SaveChanges();
        }

        static void UpdateCustomer(string customerId, string newCompanyName)
        {
            NorthwindEntities entities = new NorthwindEntities();
            Customer customer = GetCustomerById(customerId, entities);
            customer.CompanyName = newCompanyName;
            entities.SaveChanges();
        }

        static Customer GetCustomerById( string customerID, NorthwindEntities entities)
        {
            Customer result = entities.Customers.FirstOrDefault(customer => customer.CustomerID == customerID);
            return result;
        }

        static List<Customer> GetCustomersWhoOrderdIn1997ToCanada()
        {
            NorthwindEntities ne = new NorthwindEntities();

            var foundCustomers =
                from order in ne.Orders
                where order.ShipCountry == "Canada" && order.OrderDate.Value.Year == 1997
                select order.Customer;

            return foundCustomers.Distinct().ToList();
        }

        static List<Customer> GetCustomersWhoOrderedIn1997ToCanadaNative()
        {
            NorthwindEntities ne = new NorthwindEntities();
            string command = @"SELECT * FROM
                                Customers
                                WHERE CustomerID in
                                (SELECT c.CustomerID
                                FROM Customers c
                                INNER JOIN Orders o ON o.CustomerID = c.CustomerID
                                WHERE YEAR(o.OrderDate) = 1997 and o.ShipCountry = 'Canada'
                                GROUP BY c.CustomerID)";
            var result= ne.ExecuteStoreQuery<Customer>(command, new object[]{});

            return result.ToList();
        }

        static List<Order> FindSalesBySpecifiedRegionAndPeriod(string region, DateTime start, DateTime end)
        {
            NorthwindEntities ne = new NorthwindEntities();

            var result = 
                from sale in ne.Orders
                where sale.ShipRegion == region && sale.ShippedDate >= start && sale.ShippedDate <= end
                select sale;

            return result.ToList();
        }

        static void CreateDatabaseWithTheSameStructure()
        {
            NorthwindEntities ne = new NorthwindEntities();

            string createNewDateBase = "CREATE DATABASE NorthwindTwin";
            ne.ExecuteStoreCommand(createNewDateBase);

            SqlConnection connection = new SqlConnection(@"Server=.\SQL_SERVER_2008; Database=NorthwindTwin; Integrated Security = true");
            connection.Open();
            using (connection)
            {
                string createTable = ne.CreateDatabaseScript();
                SqlCommand createTableCommand = new SqlCommand(createTable, connection);
                createTableCommand.ExecuteNonQuery();
            }
        }

        static void CreateDatabaseWithTheSameStructureUsingContexBuilder()
        {
            SqlConnection connection = new SqlConnection(@"Server=.\SQL_SERVER_2008; Database=NorthwindTwin; Integrated Security = true");
            var builder = new ModelBuilder();
            builder.DiscoverEntitiesFromContext(typeof(NorthwindEntities));
            var model = builder.CreateModel();
            var context = model.CreateObjectContext<NorthwindEntities>(connection);

            
        }


        static void Main()
        {
            CreateDatabaseWithTheSameStructureUsingContexBuilder();
        }
    }
}
